﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.Utils.Menu;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;
using ERP_RORZE_DLL.Product.DAO;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_DLL.Product.Base;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_PRODUCT.Product.BusOder;
using ERP_RORZE_SYSTEM;
using BaseDAO = ERP_RORZE_TOOL.DBAccess.BaseDAO;
using Utilities = ERP_RORZE_TOOL.Common.Utilities;

namespace ERP_RORZE_PRODUCT.Product.PartHandling
{
    public partial class ctrlHandInspDetail : DevExpress.XtraEditors.XtraUserControl
    {
        ClsPartHandlingDAO clsDAO = new ClsPartHandlingDAO();
        ClsDHandInspTable clsobj = new ClsDHandInspTable();
        DataTable dt;
        DXPopupMenu menue;
        private LogUtils log;
        DXPopupMenu menu;

        public ctrlHandInspDetail()
        {
            InitializeComponent();

            log = new LogUtils();

            InitDateRangeMenu();
            InitPopupMenu();
            this.dropbtDateRange.DropDownControl = menu;
            dropbtExport.DropDownControl = menue;
            this.dropbtDateRange.Text = "6 Months";
        }
        void Reload(object sender, EventArgs e)
        {
            BindDatatoGrid();
        }
        private void btnReload_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            clsobj.ID = int.Parse(gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString());
            clsDAO.DeleteHandInspDetail(clsobj);

            //write Log
            log.Table = LogConstant.Table.Product.Hand_Insp;
            log.Task = LogConstant.Task.Delete;
            log.Description = "MONo: " + gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MONo") + "; Date: " + gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "date");
            log.WriteLog();

            Reload(sender, e);
        }

        private void gridControl1_Load(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            btnInput_Click(sender, e);
        }

        private void btnInput_Click(object sender, EventArgs e)
        {
            frm_HandInspInput frmAdd = new frm_HandInspInput();
            frmAdd.Reload += new EventHandler(Reload);
            frmAdd.Show();
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }

        //Date range
        protected virtual void InitDateRangeMenu()
        {
            menu = new DXPopupMenu();
            menu.Items.Add(new DXMenuItem("One Month", new EventHandler(OnItemClick1Month), null, null, null, null));
            menu.Items.Add(new DXMenuItem("6 Months", new EventHandler(OnItemClick6Months), null, null, null, null));
            menu.Items.Add(new DXMenuItem("One Year", new EventHandler(OnItemClick1Year), null, null, null, null));
            menu.Items.Add(new DXMenuItem("All time", new EventHandler(OnItemClickAllTime), null, null, null, null));

        }

        void OnItemClick1Month(object sender, EventArgs e)
        {
            BindDatatoGrid(1);
            dropbtDateRange.Text = "One Month";
        }

        void OnItemClick6Months(object sender, EventArgs e)
        {
            BindDatatoGrid(2);
            dropbtDateRange.Text = "6 Months";
        }

        void OnItemClick1Year(object sender, EventArgs e)
        {
            BindDatatoGrid(3);
            dropbtDateRange.Text = "One Year";
        }

        void OnItemClickAllTime(object sender, EventArgs e)
        {
            BindDatatoGrid(0);
            dropbtDateRange.Text = "All time";
        }

        private void BindDatatoGrid(int range = 2)
        {

            Cursor.Current = Cursors.WaitCursor;
            dt = clsDAO.GetAllHandInspDetail(range);
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
            Cursor.Current = Cursors.Default;
        }

        //Export to excel button

        protected virtual void InitPopupMenu()
        {
            menue = new DXPopupMenu();
            menue.Items.Add(new DXMenuItem("Export all", new EventHandler(OnItemClickExportAll), null, null, null, null));
            menue.Items.Add(new DXMenuItem("Export selected row(s)", new EventHandler(OnItemClickExportRows), null, null, null, null));

        }
        void OnItemClickExportAll(object sender, EventArgs e)
        {
            Utilities.ExportToExcel(gridView1, 1, true);
        }
        void OnItemClickExportRows(object sender, EventArgs e)
        {
            Utilities.ExportToExcel(gridView1, 0, true);
        }

        private void ImportData()
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location =
                        new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2,
                                  parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("Select [Date],[StaffID],[MONo],[OKQty] ,[NGQty] " +
                                         ",[Note] ,[Finished] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    int line = 1;

                    string Date = string.Empty;
                    string StaffID = string.Empty;
                    string MONo = string.Empty;
                    string OKQty = string.Empty;
                    string NGQty = string.Empty;
                    string Note = string.Empty;
                    string Finished = string.Empty;

                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        line++;

                        //check null
                        try
                        {
                            if (dReader["Date"].ToString().Trim().Equals(string.Empty)
                                && dReader["StaffID"].ToString().Trim().Equals(string.Empty)
                                && dReader["MONo"].ToString().Trim().Equals(string.Empty)
                                && dReader["OKQty"].ToString().Trim().Equals(string.Empty)
                                && dReader["NGQty"].ToString().Trim().Equals(string.Empty)
                                && dReader["Note"].ToString().Trim().Equals(string.Empty)
                                && dReader["Finished"].ToString().Trim().Equals(string.Empty)
                                )
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"].ToString(), "Null line"));
                                exist = true;
                            }
                        }
                        catch (Exception e)
                        {
                            countErr++;
                            listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"].ToString(), e.Message));
                            exist = true;
                        }

                        //check valid data
                        if (!exist)
                        {
                            try
                            {
                                Date = (dReader["Date"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "'" + DateTime.Parse(dReader["Date"].ToString()).ToString("yyyy/MM/dd") + "'";
                                StaffID = (dReader["StaffID"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "'" + dReader["StaffID"].ToString() + "'";
                                MONo = (dReader["MONo"].ToString().Equals(string.Empty))
                                                  ? "null"
                                                  : "'" + dReader["MONo"].ToString() + "'";

                                OKQty = (dReader["OKQty"].ToString().Equals(string.Empty))
                                             ? "null"
                                             : dReader["OKQty"].ToString();
                                NGQty = (dReader["NGQty"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : dReader["NGQty"].ToString();
                                Note = (dReader["Note"].ToString().Equals(string.Empty))
                                               ? "null"
                                               : "N'" + dReader["Note"].ToString() + "'";
                                Finished = (dReader["Finished"].ToString().Equals(string.Empty))
                                                 ? "null"
                                                 : "'" + dReader["Finished"].ToString() + "'";

                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(),
                                                                   dReader["MONo"].ToString(),
                                                                   ex.Message));
                            }
                        }

                        //Insert to database
                        if (!exist)
                        {
                            try
                            {
                                string sql = "INSERT INTO [242_DHandInsp] ("
                                         + "[date],[StaffID],[MONo],[OKQty] ,[NGQty] "
                                         + ",[Note] ,[Finished] )"
                                         + " VALUES (" + Date + ", " + StaffID + ", " + MONo + ","
                                         + OKQty + "," + NGQty + ", " + Note + ", " + Finished + ")";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);

                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                countOk++;

                                //write Log
                                log.Table = LogConstant.Table.Product.Hand_Insp;
                                log.Task = LogConstant.Task.Import;
                                log.Description = "MONo: " + MONo + "; Date: " + Date;
                                log.WriteLog();

                            }
                            catch (Exception ex)
                            {
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"].ToString(), ex.Message));
                            }

                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    //MessageBox.Show(i + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")\nLines have error OptionID: " + lineNo);
                    //LoadData();
                    Frm_ImportReport report = new Frm_ImportReport(countOk, listDup, listErr, "HandInsp", "MONo");
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    progressPanel1.Visible = false;
                    MessageBox.Show(ex.Message);
                }

            }
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            ImportData();
        }

        private void gridView1_ValidateRow(object sender, DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs e)
        {
            GridView view = sender as GridView;

            //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
            if (view.IsNewItemRow(e.RowHandle))
            {
                //e.RowHandle trả về giá trị int là thứ tự của dòng hiện tại

            }
            //Cũ thì update
            else
            {
                string temDate = (view.GetRowCellValue(e.RowHandle, view.Columns["date"]).ToString().Trim() != string.Empty) ? DateTime.Parse(view.GetRowCellValue(e.RowHandle, view.Columns["date"]).ToString()).ToString("yyyy/MM/dd") : view.GetRowCellValue(e.RowHandle, view.Columns["date"]).ToString().Trim();

                string date = (temDate == "0001/01/01" || temDate == "") ? "null" : "'" + DateTime.Parse(view.GetRowCellValue(e.RowHandle, view.Columns["date"]).ToString()).ToString("yyyy/MM/dd") + "'";

                string Finished = (view.GetRowCellValue(e.RowHandle, view.Columns["Finished"]).ToString().Trim() == "") ? "null" : "'" + view.GetRowCellValue(e.RowHandle, view.Columns["Finished"]).ToString() + "'";
                string MONo = (view.GetRowCellValue(e.RowHandle, view.Columns["MONo"]).ToString().Trim() == "") ? "null" : "'" + view.GetRowCellValue(e.RowHandle, view.Columns["MONo"]).ToString() + "'";
                string StaffID = (view.GetRowCellValue(e.RowHandle, view.Columns["StaffID"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["StaffID"]).ToString() + "'";
                string OKQty = (view.GetRowCellValue(e.RowHandle, view.Columns["OKQty"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["OKQty"]).ToString();
                string NGQty = (view.GetRowCellValue(e.RowHandle, view.Columns["NGQty"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["NGQty"]).ToString();
                string Note = (view.GetRowCellValue(e.RowHandle, view.Columns["Note"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["Note"]).ToString() + "'";
                string Id = (view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString();

                string update = "update [242_DHandInsp] set [date]=" + date
                    + ", [MONo] = " + MONo+ ", [StaffID] = " + StaffID
                    + ", [OKQty] = " + OKQty
                    + ", [NGQty] = " + NGQty
                    + ", [Note] = " + Note
                    + ", [Finished] = " + Finished
                    + " where [ID] = " + Id;
                if (BaseDAO.ExecuteNonQuery(update) > 0)
                {
                    //write Log
                    log.Table = LogConstant.Table.Product.Hand_Insp;
                    log.Task = LogConstant.Task.Update;
                    log.Description = "MONo: " + MONo + "; Date: " + date;
                    log.WriteLog();

                }

            }
        }

        private void gridView1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Alt)
            {
                if (e.KeyCode == Keys.E)
                {
                    if (gridView1.OptionsBehavior.Editable)
                    {
                        gridView1.OptionsBehavior.Editable = false;
                    }
                    else
                    {
                        gridView1.OptionsBehavior.Editable = true;
                        foreach (GridColumn column in gridView1.Columns)
                        {
                            if (column.FieldName.Equals("ID")){
                                column.OptionsColumn.AllowEdit = false;
                            }
                        }
                    }
                }
            }
        }

    }
}
